﻿using AutoMapper;
using BasicData.Domain.Entities;
using BasicData.Infrastructure.Repositories;
using Quartz;
using SqlSugar;
using StackExchange.Redis;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;
using Warehouse.Application.Dto.Input;
using Warehouse.Application.Dto.Output;
using Warehouse.Application.Services.Libraryrecord;
using Warehouse.Domain.Entities;
using Warehouse.Infrastructure.Repositories;
using Warehouse.Result;
using Yitter.IdGenerator;

namespace Warehouse.Application.Services.Libraryrecord
{
    public class LibraryrecordAppService: ILibraryrecordAppService
    {

        private readonly IWarehouseModelRepository<LibraryrecordModel> _libraryrecordModelRepository;
        private readonly IMapper _mapper;
        private readonly IWarehouseModelRepository<StorageLocationModel> _storageLocationModelRepository;
        private readonly IBasicModelRepository<ProductModel> _productModelRepository;
        private readonly IWarehouseModelRepository<SpringWaterModel> _springWateModelRepository;
        private readonly IConnectionMultiplexer _redis;
        private readonly IDatabase _redisDb;
        private const string YEARLY_STATS_KEY = "warehouse:stats:yearly";
        private const int CACHE_EXPIRY_HOURS = 24; // 缓存24小时


        private readonly IBasicModelRepository<BOMModel> _bomModelRepository;
        private readonly IWarehouseModelRepository<WideTableModel> _wideTableModelRepository;


        public LibraryrecordAppService(IWarehouseModelRepository<LibraryrecordModel> libraryrecordModelRepository,
            IMapper mapper,
            IWarehouseModelRepository<StorageLocationModel> storageLocationModelRepository,
            IBasicModelRepository<ProductModel> productModelRepository,
            IWarehouseModelRepository<SpringWaterModel> springWateModelRepository,
            IConnectionMultiplexer redis,
            IBasicModelRepository<BOMModel> bomModelRepository,
            IWarehouseModelRepository<WideTableModel> wideTableModelRepository)
        {
            _libraryrecordModelRepository = libraryrecordModelRepository;
            _mapper = mapper;
            _storageLocationModelRepository = storageLocationModelRepository;
            _productModelRepository = productModelRepository;
            _springWateModelRepository = springWateModelRepository;
            _redis = redis;
            _redisDb = redis.GetDatabase();
            _bomModelRepository = bomModelRepository;
            _wideTableModelRepository = wideTableModelRepository;
        }




        /// <summary>
        /// 添加出入库的库存
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public ApiResult<int> CreateLibraryrecord(CreateLibraryrecordInput input)
        {
            var model = _mapper.Map<LibraryrecordModel>(input);
            model.Id = YitIdHelper.NextId();
            model.EnterTime = DateTime.Now;
            model.IsDeleted = false;
            model.CreatedAt = DateTime.Now;

            // 使用 Any 替代 FirstOrDefault 提高性能
            bool exists = _libraryrecordModelRepository.GetQuery()
                .Any(x => x.Oddnumber == input.Oddnumber && x.IsDeleted == false);
            if (exists)
            {
                return new ApiResult<int>()
                {
                    Code = ApiEnum.Fail,
                    Message = "入库单号已存在"
                };
            }
            
            var b = new SpringWaterModel()
            {
                Id = YitIdHelper.NextId(),
                Oddnumber = model.Oddnumber,
                Operation = model.Operation,
                Receiptstype = model.Receiptstype,
                Product = model.Product,
                StorageLocationId = model.StorageLocationId,
                LNumber = model.LibraryNumber,
                Described = model.Receiptstype,
                CreatedAt = DateTime.Now,
            };
            using (TransactionScope tran = new TransactionScope())
            {
                try
                {
                    _libraryrecordModelRepository.Create(model);
                    _springWateModelRepository.Create(b);
                    tran.Complete(); // 添加此行，表示事务成功完成
                }
                catch (Exception ex)
                {
                    Console.WriteLine("事务失败: " + ex.Message);
                    return new ApiResult<int>()
                    {
                        Code = ApiEnum.Fail,
                        Message = "添加入库单失败"
                    };
                }
            }

                return new ApiResult<int>()
                {
                    Code = ApiEnum.Ok,
                    Message = "添加入库单成功",
                    Data = 1
                };
        }

        /// <summary>
        /// 获取出入库的库存
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public ApiPaing<LibraryrecordOutput> GetLibraryrecord(LibraryrecordInput input)
        {
            // 使用 SqlSugar 的链式查询
            var query = _libraryrecordModelRepository.GetQuery()
                .Where(x => x.IsDeleted == false);
            
            // 添加条件查询
            if (!string.IsNullOrEmpty(input.Receiptstype))
            {
                query = query.Where(x => x.Receiptstype == input.Receiptstype);
            }
            if (input.Product > 0)
            {
                query = query.Where(x => x.Product == input.Product);
            }
            if (input.StorageLocationId > 0)
            {
                query = query.Where(x => x.StorageLocationId == input.StorageLocationId);
            }
            if (input.BeginTime != null && input.EndTime != null)
            {
                query = query.Where(x => x.EnterTime >= input.BeginTime && x.EnterTime <= input.EndTime);
            }

            // 使用 SqlSugar 的 Join 方法
            var result = query.OrderByDescending(x => x.Id)
                .LeftJoin<StorageLocationModel>((x, y) => x.StorageLocationId == y.Id && y.IsDeleted == false)
                .LeftJoin<ProductModel>((x, y, z) => x.Product == z.Id && z.IsDeleted == false)
                .Select((x, y, z) => new LibraryrecordOutput
                {
                    Id = x.Id,
                    Oddnumber = x.Oddnumber,
                    Operation = x.Operation,
                    Receiptstype = x.Receiptstype,
                    Product = x.Product,
                    productName = z.ProductName,
                    StorageLocationId = x.StorageLocationId,
                    StorageLocationName = y.StorageLocationName,
                    EnterTime = x.EnterTime,
                    LibraryNumber = x.LibraryNumber,
                    OutNumber = x.OutNumber,
                    Outdescr = x.Outdescr,
                    OutTime = x.OutTime
                });

            // 使用 ToPageList 一次性获取分页数据和总数
            int totalCount = 0;
            var list = result.ToPageList(input.PageIndex, input.PageSize, ref totalCount);

            return new ApiPaing<LibraryrecordOutput>
            {
                TotalCount = totalCount,
                PageDatas = list,
                Code = ApiEnum.Ok,
                Message = "查询成功"
            };
        }



        /// <summary>
        /// 出库
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public ApiResult<int> OutLibraryrecord(OutLibraryrecordInput input)
        {
            // 使用 AsQueryable 更直观地构建查询
            var list = _libraryrecordModelRepository.GetQuery()
                .Where(x => x.Id == input.Id && x.IsDeleted == false)
                .First();
            
            list.OutTime= DateTime.Now;
            if((int)input.OutNumber> (int)list.LibraryNumber)
            {
                return new ApiResult<int>
                {
                    Code = ApiEnum.Fail,
                    Message = "出库数量不能大于入库数量"
                };
            }
            list.LibraryNumber= (int)list.LibraryNumber - (int)input.OutNumber;
            list.OutNumber = (int)(list.OutNumber + input.OutNumber);

            var model =_mapper.Map(input, list);


            var spring = new SpringWaterModel()
            {
                Id = YitIdHelper.NextId(),
                Oddnumber = model.Oddnumber,
                Operation ="出库",
                Receiptstype = input.Receiptstype,
                Product = model.Product,
                StorageLocationId = model.StorageLocationId,
                LNumber = (int)input.OutNumber,
                Described = input.Outdescr,
                CreatedAt = DateTime.Now,
            };


            using (TransactionScope tran = new TransactionScope())
            {
                try
                {
                     _libraryrecordModelRepository.Update(model);
                    _springWateModelRepository.Create(spring);
                    tran.Complete(); // 添加此行，表示事务成功完成
                }
                catch (Exception ex)
                {
                    Console.WriteLine("事务失败: " + ex.Message);
                    return new ApiResult<int>
                    {
                        Code = ApiEnum.Fail,
                        Message = "出库失败"
                    };
                }
            }

       
            return new ApiResult<int>
            {
                Code = ApiEnum.Ok,
                Message = "出库成功",
                Data = 1
            };

        }



        /// <summary>
        /// 流水查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public ApiPaing<SpringWaterOutput> GetApringWater(GetApringWaterInput input)
        {
            var query = _springWateModelRepository.GetQuery()
                .Where(x => x.IsDeleted == false);
            
            if (!string.IsNullOrEmpty(input.Oddnumber))
            {
                query = query.Where(x => x.Oddnumber.Contains(input.Oddnumber));
            }
            if(!string.IsNullOrEmpty(input.Receiptstype))
            {
                query = query.Where(x => x.Receiptstype == input.Receiptstype);
            }
            if(input.BeginTime != null && input.EndTime != null)
            {
                query = query.Where(x => x.CreatedAt >= input.BeginTime && x.CreatedAt <= input.EndTime);
            }
            
            var result = query.OrderByDescending(x => x.Id)
                .LeftJoin<StorageLocationModel>((x, y) => x.StorageLocationId == y.Id && y.IsDeleted == false)
                .LeftJoin<ProductModel>((x, y, z) => x.Product == z.Id && z.IsDeleted == false)
                .Select((x, y, z) => new SpringWaterOutput
                {
                    Id = x.Id,
                    Oddnumber = x.Oddnumber,
                    Operation = x.Operation,
                    Receiptstype = x.Receiptstype,
                    Product = x.Product,
                    productName = z.ProductName,
                    StorageLocationId = x.StorageLocationId,
                    StorageLocationName = y.StorageLocationName,
                    Described = x.Described,
                    LNumber = x.LNumber,
                    CreatedAt = x.CreatedAt
                });
            
            // 使用 ToPageList 一次性获取分页数据和总数
            int totalCount = 0;
            var model = result.ToPageList(input.PageIndex, input.PageSize, ref totalCount);
            
            return new ApiPaing<SpringWaterOutput>
            {
                Code = ApiEnum.Ok,
                Message = "查询成功",
                PageDatas = model,
                TotalCount = result.Count()
            };
        }



        /// <summary>
        /// 获取一年时间生成柱状图（按月统计LNumber总和和数据量）- 带Redis缓存
        /// </summary>
        /// <returns></returns>
        public object PayMoney()
        {
            // 尝试从Redis获取缓存数据
            string cacheKey = $"{YEARLY_STATS_KEY}:{DateTime.Now.ToString("yyyy-MM-dd")}";
            var cachedData = _redisDb.StringGet(cacheKey);
            
            if (!cachedData.IsNull)
            {
                // 如果有缓存，直接返回缓存数据
                return System.Text.Json.JsonSerializer.Deserialize<object>(cachedData);
            }
            
            // 没有缓存，从数据库查询
            // 计算日期范围：今天到前一年
            var endDate = DateTime.Now.Date;
            var startDate = endDate.AddYears(-1).AddDays(1); // 一年前的后一天

            // 生成所有月份
            var allMonths = Enumerable.Range(0, 12)
                .Select(i => startDate.AddMonths(i).ToString("yyyy-MM"))
                .Distinct()
                .ToList();

            // 查询数据库获取这段时间内有记录的数据
            var list = _springWateModelRepository.GetQuery()
                .Where(x => !x.IsDeleted && 
                       x.CreatedAt.Date <= endDate && 
                       x.CreatedAt.Date > startDate && x.Operation=="出库")
                .ToList();

            // 按月份分组并计算统计信息
            var existingData = list.GroupBy(x => x.CreatedAt.ToString("yyyy-MM"))
                .ToDictionary(g => g.Key, g => new { 
                    Sum = g.Sum(x => x.LNumber),
                    Count = g.Count() 
                });

            // 合并所有月份，对没有数据的月份数值为0
            var result = allMonths.Select(month => new
            {
                Date = month,
                Sum = existingData.ContainsKey(month) ? existingData[month].Sum : 0,
                Count = existingData.ContainsKey(month) ? existingData[month].Count : 0
            }).OrderBy(x => x.Date).ToList();
            
            // 将结果存入Redis缓存
            var jsonResult = System.Text.Json.JsonSerializer.Serialize(result);
            _redisDb.StringSet(cacheKey, jsonResult, TimeSpan.FromHours(CACHE_EXPIRY_HOURS));
            
            return result;
        }

        /// <summary>
        /// 获取7天时间生成柱状图 - 带Redis缓存
        /// </summary>
        /// <returns></returns>
        public object PayMoneyday()
        {
            // 尝试从Redis获取缓存数据
            string cacheKey = $"warehouse:stats:daily:{DateTime.Now.ToString("yyyy-MM-dd")}";
            var cachedData = _redisDb.StringGet(cacheKey);
            
            if (!cachedData.IsNull)
            {
                // 如果有缓存，直接返回缓存数据
                return System.Text.Json.JsonSerializer.Deserialize<object>(cachedData);
            }
            
            // 计算日期范围：今天到前6天
            var endDate = DateTime.Now.Date;
            var startDate = endDate.AddDays(-6);
            
            // 生成所有7天的日期
            var allDates = Enumerable.Range(0, 7)
                .Select(i => startDate.AddDays(i).ToString("yyyy-MM-dd"))
                .ToList();
            
            // 查询数据库获取这段时间内的记录
            var list = _springWateModelRepository.GetQuery()
                .Where(x => !x.IsDeleted && 
               x.CreatedAt.Date >= startDate && 
               x.CreatedAt.Date <= endDate)
                .ToList();
            
            // 按日期分组并计算统计信息
            var existingData = list.GroupBy(x => x.CreatedAt.ToString("yyyy-MM-dd"))
                .ToDictionary(g => g.Key, g => new { 
                    Sum = g.Sum(x => x.LNumber),
                    Count = g.Count() 
                });
            
            // 合并所有日期，对没有数据的日期数值为0
            var result = allDates.Select(date => new
            {
                Date = date,
                Sum = existingData.ContainsKey(date) ? existingData[date].Sum : 0,
                Count = existingData.ContainsKey(date) ? existingData[date].Count : 0
            }).OrderBy(x => x.Date).ToList();
            
            // 将结果存入Redis缓存
            var jsonResult = System.Text.Json.JsonSerializer.Serialize(result);
            _redisDb.StringSet(cacheKey, jsonResult, TimeSpan.FromHours(CACHE_EXPIRY_HOURS));
            
            return result;
        }

        /// <summary>
        /// 获取30天时间生成柱状图 - 带Redis缓存
        /// </summary>
        /// <returns></returns>
        public object PayMoneyMonth()
        {
            // 尝试从Redis获取缓存数据
            string cacheKey = $"warehouse:stats:monthly:{DateTime.Now.ToString("yyyy-MM-dd")}";
            var cachedData = _redisDb.StringGet(cacheKey);
            
            if (!cachedData.IsNull)
            {
                // 如果有缓存，直接返回缓存数据
                return System.Text.Json.JsonSerializer.Deserialize<object>(cachedData);
            }
            
            // 计算日期范围：今天的前一天到前30天
            var endDate = DateTime.Now.Date.AddDays(-1);
            var startDate = endDate.AddDays(-29); // 总共30天
            
            // 生成这30天的所有日期
            var allDates = Enumerable.Range(0, 30)
                .Select(offset => startDate.AddDays(offset).ToString("yyyy-MM-dd"))
                .ToList();
            
            // 查询数据库获取这段时间内有记录的数据
            var list = _springWateModelRepository.GetQuery()
                .Where(x => !x.IsDeleted &&
               x.CreatedAt.Date >= startDate &&
               x.CreatedAt.Date <= endDate)
                .ToList();
            
            // 按日期分组统计
            var existingData = list.GroupBy(x => x.CreatedAt.ToString("yyyy-MM-dd"))
                .ToDictionary(g => g.Key, g => new { 
                    Sum = g.Sum(x => x.LNumber),
                    Count = g.Count() 
                });
            
            // 合并所有日期，对没有数据的日期数值为0
            var result = allDates.Select(date => new
            {
                Date = date,
                Sum = existingData.ContainsKey(date) ? existingData[date].Sum : 0,
                Count = existingData.ContainsKey(date) ? existingData[date].Count : 0
            }).OrderBy(x => x.Date).ToList();
            
            // 将结果存入Redis缓存
            var jsonResult = System.Text.Json.JsonSerializer.Serialize(result);
            _redisDb.StringSet(cacheKey, jsonResult, TimeSpan.FromHours(CACHE_EXPIRY_HOURS));
            
            return result;
        }


        /// <summary>
        /// 更新每日仓库统计数据
        /// </summary>
        /// <param name="date">指定日期，默认为今天</param>
        /// <returns></returns>
        public async Task<ApiResult<bool>> UpdateDailyStatistics(DateTime? date = null)
        {
            // 获取指定日期，默认为当天
            DateTime targetDate = date ?? DateTime.Now.Date;
            DateTime nextDate = targetDate.AddDays(1);

            try
            {
                // 查询当天入库记录
                var inRecords = _springWateModelRepository.GetQuery()
                    .Where(x => x.IsDeleted == false &&
                           x.CreatedAt >= targetDate &&
                           x.CreatedAt < nextDate &&
                           x.Operation == "入库")
                    .ToList();

                // 查询当天出库记录
                var outRecords = _springWateModelRepository.GetQuery()
                    .Where(x => x.IsDeleted == false &&
                           x.CreatedAt >= targetDate &&
                           x.CreatedAt < nextDate &&
                           x.Operation == "出库")
                    .ToList();

                // 计算入库和出库数量
                int inCount = inRecords.Count;
                int outCount = outRecords.Count;

                // 计算总金额
                decimal totalOutAmount = 0;
                decimal totalInAmount = 0;

                // 计算出库金额
                foreach (var record in outRecords)
                {
                    var bomInfo = _bomModelRepository.GetQuery()
                        .Where(b => b.Id == record.Product && b.IsDeleted == false)
                        .First();

                    if (bomInfo != null)
                    {
                        totalOutAmount += record.LNumber * Convert.ToDecimal(bomInfo.BomMoney);
                    }
                }

                // 计算入库金额
                foreach (var record in inRecords)
                {
                    var bomInfo = _bomModelRepository.GetQuery()
                        .Where(b => b.Id == record.Product && b.IsDeleted == false)
                        .First();

                    if (bomInfo != null)
                    {
                        totalInAmount += record.LNumber * Convert.ToDecimal(bomInfo.BomMoney);
                    }
                }

                // 检查是否已存在当天记录
                var existingRecord = _wideTableModelRepository.GetValue ()
                    .Where(x => x.CreatedAt == targetDate && x.IsDeleted == false)
                    .FirstOrDefault();

                if (existingRecord != null)
                {
                    // 更新现有记录
                    existingRecord.LibraryNumber  = inCount;
                    existingRecord.OutNumber = outCount;
                    existingRecord.BomMoneyNum = totalOutAmount;
                    existingRecord.LibraryMoneyNum = totalInAmount;
                    existingRecord.UpdatedAt = DateTime.Now;

                    _wideTableModelRepository.Update(existingRecord);
                }
                else
                {
                    // 创建新记录
                    var newRecord = new WideTableModel
                    {
                        Id = YitIdHelper.NextId(),
                        LibraryNumber  = inCount,
                        OutNumber  = outCount,
                        BomMoneyNum  = totalOutAmount,
                        LibraryMoneyNum  = totalInAmount,
                        CreatedAt = targetDate,
                        IsDeleted = false
                    };

                    _wideTableModelRepository.Create(newRecord);
                }

                return new ApiResult<bool>
                {
                    Code = ApiEnum.Ok,
                    Message = "每日统计数据更新成功",
                    Data = true
                };
            }
            catch (Exception ex)
            {
                return new ApiResult<bool>
                {
                    Code = ApiEnum.Fail,
                    Message = $"统计数据更新失败: {ex.Message}",
                    Data = false
                };
            }
        }

        // 添加定时任务触发器方法
        public async Task ScheduleDailyStatisticsUpdate()
        {
            // 此方法可以被定时任务框架调用
            await UpdateDailyStatistics();
        }


        /// <summary>
        /// 查询当天的统计
        /// </summary>
        /// <param name="Jtime"></param>
        /// <returns></returns>
        public ApiResult<WideTableModel> GetWideTable(DateTime Jtime)
        {
            var quwery = DateTime.Now.Date;
              Jtime = quwery;
            var list = _wideTableModelRepository.GetValue()
                .Where(x => x.CreatedAt.Date == quwery).FirstOrDefault();
            return new ApiResult<WideTableModel>
            {
                Code = ApiEnum.Ok,
                Data = list,
                Message = "查询成功"
            };
        }

        /// <summary>
        /// 定时任务：每天凌晨清除并更新Redis缓存数据
        /// </summary>
        /// <returns></returns>
        public async Task RefreshRedisCacheDaily()
        {
            try
            {
                // 删除所有相关的Redis缓存键
                var yearlyStatsKey = $"{YEARLY_STATS_KEY}:{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}";
                var dailyStatsKey = $"warehouse:stats:daily:{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}";
                var monthlyStatsKey = $"warehouse:stats:monthly:{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}";
                
                // 删除旧缓存
                await _redisDb.KeyDeleteAsync(yearlyStatsKey);
                await _redisDb.KeyDeleteAsync(dailyStatsKey);
                await _redisDb.KeyDeleteAsync(monthlyStatsKey);
                
                // 重新生成缓存数据
                PayMoney(); // 生成年度统计
                PayMoneyday(); // 生成7天统计
                PayMoneyMonth(); // 生成30天统计
                
                // 更新每日统计数据
                await UpdateDailyStatistics(DateTime.Now.AddDays(-1));
                
                Console.WriteLine($"Redis缓存数据刷新成功: {DateTime.Now}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Redis缓存数据刷新失败: {ex.Message}");
            }
        }


    }

}


